package fr.inserm.u674.chcdbweb.model.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import static fr.inserm.u674.chcdbweb.model.DAO.DAOUtil.*;
import fr.inserm.u674.chcdbweb.model.DTO.Tissue;
import fr.inserm.u674.chcdbweb.model.DTO.TissueExtraction;
import fr.inserm.u674.chcdbweb.model.DTO.TissueExtractionRNA;

public class TissueExtractionRNADAO extends TissueExtractionDAO {

	// CONSTANTS ----------------------------------------------------------------------------------

	private static final String SQL_FIND_BY_EXTRACTIONID =
		"SELECT * FROM Extraction INNER JOIN RNAExtractionInfo "
			+ "ON (Extraction.extractionID =  RNAExtractionInfo.Extraction_extractionID) "
			+ "WHERE extractionID = ?";
	private static final String SQL_LIST_ORDER_BY_CHCID_THEN_DATE =
		"SELECT * FROM Extraction INNER JOIN RNAExtractionInfo "
			+ "ON (Extraction.extractionID =  RNAExtractionInfo.Extraction_extractionID) "
			+ "ORDER BY tissue_chcID, dateExtraction";
	private static final String SQL_LIST_TISSUE_EXTRACTIONS =
		"SELECT * FROM Extraction INNER JOIN RNAExtractionInfo "
			+ "ON (Extraction.extractionID =  RNAExtractionInfo.Extraction_extractionID) "
			+ "WHERE tissue_chcID = ? AND tissue_tissueTypeID = ?";

	// VARS ---------------------------------------------------------------------------------------

	private DAOFactory daoFactory;

	public TissueExtractionRNADAO(DAOFactory daoFactory) {
		super(daoFactory);
	}

	// ACTIONS ------------------------------------------------------------------------------------

	public TissueExtractionRNA find(Integer extractionID) throws DAOException {
		return find(SQL_FIND_BY_EXTRACTIONID, extractionID);
	}

	private TissueExtractionRNA find(String sql, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		TissueExtractionRNA rnaExtraction = null;
		try {
			connection = daoFactory.getConnection();
			preparedStatement =
				prepareStatement(connection, SQL_FIND_BY_EXTRACTIONID, false, values);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) rnaExtraction = mapRNAExtraction(resultSet);

			return rnaExtraction;
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement, resultSet);
		}

	}

	public List<TissueExtraction> list(ArrayList<Tissue> tissuesOfInterest) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<TissueExtraction> rnaExtractions = new ArrayList<TissueExtraction>();
		try {
			connection = daoFactory.getConnection();
			preparedStatement = connection.prepareStatement(SQL_LIST_ORDER_BY_CHCID_THEN_DATE);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next())
				rnaExtractions.add(mapRNAExtraction(resultSet));
			return rnaExtractions;
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement, resultSet);
		}

	}

	public List<TissueExtraction> listTissueExtractions(Tissue tissue) throws DAOException {
		Object[] values = { tissue.getChcID(), tissue.getTissueTypeID() };
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<TissueExtraction> rnaExtractions = new ArrayList<TissueExtraction>();
		try {
			connection = daoFactory.getConnection();
			preparedStatement =
				prepareStatement(connection, SQL_LIST_TISSUE_EXTRACTIONS, false, values);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next())
				rnaExtractions.add(mapRNAExtraction(resultSet));
			return rnaExtractions;
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement, resultSet);
		}
	}

	// HELPERS ------------------------------------------------------------------------------------

	private static TissueExtractionRNA mapRNAExtraction(ResultSet resultSet) throws SQLException {
		/* return new RNAExtraction(new Tissue(resultSet.getInt("tissue_chcID"), resultSet
		 * .getString("tissue_tissueTypeID")), new User(resultSet.getString("experimenter")),
		 * resultSet.getDate("dateExtraction"), resultSet.getFloat("finalVolume_muL"), resultSet
		 * .getFloat("concentration_ng_muL"), resultSet.getFloat("quantity_mug"), resultSet
		 * .getFloat("do260_do280"), resultSet.getDate("agilentDate"), resultSet
		 * .getFloat("agilent_28S_18S"), resultSet.getString("extractionMethod"), resultSet
		 * .getString("qualityExtractionGel"));
		 */

		TissueExtractionRNA rnaExtraction = (TissueExtractionRNA) mapTissueExtraction(resultSet, false);
		rnaExtraction.setAgilent28S18S(resultSet.getFloat("agilent_28S_18S"));
		rnaExtraction.setDo260do280(resultSet.getFloat("do260_do280"));
		rnaExtraction.setAgilentDate(resultSet.getDate("agilentDate"));
		rnaExtraction.setExtractionMethod(resultSet.getString("extractionMethod"));
		return rnaExtraction;
	}
}
